package com.sq.partner.manager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;


import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradNoCarRiskProdDao;
import com.sq.partner.manager.mybatis.model.NocarProdDefalut;
import com.sq.partner.manager.mybatis.model.NocarRisk;
import com.sq.partner.manager.mybatis.model.NocarRiskCal;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;

import java.util.List;
import java.util.Map;
@Repository
public class TradNoCarRiskProdDaoImpl extends BaseDaoImpl implements ITradNoCarRiskProdDao {
	private static Logger logger = LogUtil.getLog(TradNoCarRiskProdDaoImpl.class);

	@Override
	public Page<NocarRisk> queryRiskPlanBasePage(Page<NocarRisk> page,
			String prodCode) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.RISK_CODE AS RISKCODE, T.PROD_CODE AS PRODCODE, T.RISK_NAME AS RISKNAME, T.STATUS AS STATUS,"+
				       "T.C_FIELD_1 AS CFIELD1,  H.CODE_NAME AS CFIELD1DESC, K.CODE_NAME AS CFIELD1DESC09C, T.C_FIELD_2 AS CFIELD2,J.CODE_NAME AS CFIELD2DESC, T.C_FIELD_3 AS CFIELD3,  T.C_FIELD_4 AS CFIELD4, T.C_FIELD_5 AS CFIELD5,"+
				       "T.C_FIELD_6 AS CFIELD6,T.C_FIELD_7 AS CFIELD7, T.C_FIELD_8 AS CFIELD8, T.C_FIELD_9 AS CFIELD9,T.C_FIELD_10 AS CFIELD10,"+
				       "T.N_FIELD_1 AS NFIELD1,T.N_FIELD_2 AS NFIELD2, T.N_FIELD_3 AS NFIELD3,T.N_FIELD_4 AS NFIELD4,T.N_FIELD_5 AS NFIELD5,"+
				       "T.N_FIELD_6 AS NFIELD6,T.N_FIELD_7 AS NFIELD7,T.N_FIELD_8 AS NFIELD8,T.N_FIELD_9 AS NFIELD9,T.N_FIELD_10*1000 AS NFIELD10, T.N_FIELD_11*1000 AS NFIELD11,"+
				       "T.N_FIELD_12 AS NFIELD12,T.N_FIELD_13 AS NFIELD13,T.N_FIELD_14 AS NFIELD14, T.N_FIELD_15 AS NFIELD15, T.D_FIELD_1 AS DFIELD1,"+
				       "T.D_FIELD_2 AS DFIELD2,T.D_FIELD_3 AS DFIELD3, T.D_FIELD_4 AS DFIELD4,T.D_FIELD_5 AS DFIELD5, T.N_FIELD_16 * 100 AS NFIELD16,"+
				       "T.N_FIELD_17 * 1000 AS NFIELD17,T.N_FIELD_18 * 1000 AS NFIELD18,T.N_FIELD_19 AS NFIELD19,T.N_FIELD_20 AS NFIELD20,T.RISK_CAL_TYPE AS RISKCALTYPE,"+
				       "W.CODE_NAME AS RISKCALDSCP, T.AMOUNT AS AMOUNT ,M.CODE_NAME AS CFIELD1DESC012B,X.CODE_NAME AS CFIELD2DESC012B,"
				       + "  F.CODE_NAME AS cField1Desc081F  , Ma.CODE_NAME AS masterName "
				       + " FROM T_NOCAR_RISK T INNER JOIN T_GATEWAY_DICT W ON T.RISK_CAL_TYPE = W.CODE"+
				       " AND W.CODE_TYPE = 'nocar_risk_cal_type'  LEFT JOIN T_GATEWAY_DICT H ON H.CODE = T.C_FIELD_1 AND H.CODE_TYPE = 'insure_item_012a'   "
				       + " LEFT JOIN T_GATEWAY_DICT K ON K.CODE = T.C_FIELD_1 AND K.CODE_TYPE = 'insure_item_090c' "
				       + " LEFT JOIN T_GATEWAY_DICT J ON J.CODE = T.C_FIELD_2 AND J.CODE_TYPE = 'insure_val_aff_typ'   "
				       + " LEFT JOIN T_GATEWAY_DICT M ON M.CODE = T.C_FIELD_1 AND M.CODE_TYPE = 'insure_item_012b'   "
				       + " LEFT JOIN T_GATEWAY_DICT X ON X.CODE = T.C_FIELD_2 AND X.CODE_TYPE = 'insure_val_aff_012b'   "
				       + " LEFT JOIN T_GATEWAY_DICT F ON F.CODE = T.C_FIELD_1 AND F.CODE_TYPE = 'insure_item_081f'   "

				       + " LEFT JOIN T_GATEWAY_DICT Ma ON Ma.CODE = T.C_FIELD_9 AND Ma.CODE_TYPE = 'mastter_type' "
				       + " WHERE T.PROD_CODE = ? ORDER BY T.INSERT_TIME,T.RISK_CODE DESC");
			String sql;
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			pstmt.setObject(1, prodCode);
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			pstmt1.setObject(1, prodCode );
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public void saveNocarRisk(NocarRisk noCarRisk) throws Exception {
		// TODO Auto-generated method stub
		this.add("insertNocarRisk", noCarRisk);
	}
	//该方法已经无用
	@Override
	public NocarRisk findMaxNocarRiskCode(NocarRisk nocarRisk) throws SQLException {
		// TODO Auto-generated method stub
		return (NocarRisk) this.query("queryMaxRiskProdIndexByCode", nocarRisk);
	}

	@Override
	public void updateRisk(NocarRisk noCarRisk) throws Exception {
		// TODO Auto-generated method stub
		this.update("updateNocarRisk", noCarRisk);
	} 
	@Override
	public void deleteRisk(NocarRisk noCarRisk) throws Exception {
		// TODO Auto-generated method stub
		this.delete("delNocarRisk", noCarRisk);
	}

	@Override
	public boolean isExistRisk(String prodCode, String riskCode)
			throws SQLException {
		 NocarRisk noCarRisk = new NocarRisk();
		 noCarRisk.setProdCode(prodCode);
		 noCarRisk.setRiskCode(riskCode);
		 noCarRisk = (NocarRisk) this.query("isExistRiskCodeByProdCode", noCarRisk);
		 if(noCarRisk.getIsExistRiskCount()>0)
		 {
			 return  true;
		 }
 		return false;
	}

	@Override
	public void deleteRiskByProdCode(String prodCode) throws Exception {
		// TODO Auto-generated method stub
		NocarRisk noCarRisk = new NocarRisk();
		noCarRisk.setProdCode(prodCode);
		this.delete("delNocarRiskByProdCode", noCarRisk);
	}

	@Override
	public int queryProdRiskCountByCode(String prodCode) throws SQLException {
		// TODO Auto-generated method stub
		return (Integer)this.query("queryRiskCountByProdCode", prodCode);
	}

	@Override
	public List<NocarRisk> findNocarByMap(Map<String, Object> map)
			throws SQLException {
		return this.queryList("queryNocarRiskByMap", map);
	}

	@Override
	public List<Map<String, Object>> searchNoCarRisksByprodCode(Map<String, Object> map)
			throws SQLException {
		return  this.queryList("searchNoCarRisksByprodCode", map);
	}

	
	@Override
	public  List<Map<String, Object>> searchNoCarProdDefaultsByprodCode(
			Map<String, Object> map) throws SQLException {
		// TODO Auto-generated method stub
		return  this.queryList("searchNoCarProdDefaultsByprodCode", map);
	}

	@Override
	public List<Map<String, Object>> searchNoCarRiskCalsByprodCode(Map<String, Object> map)
			throws SQLException {
		// TODO Auto-generated method stub
		return   this.queryList("searchNoCarRiskCalsByprodCode", map);
	} 
}
